package com.kris;

import java.io.File;
import java.io.IOException;
import java.util.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * 表格工具类
 *
 * @author zhangqunshi
 */
public class ExcelUtils {

    /**
     * 读取一个excel文件中指定sheet的指定列数据(主要用于读取员工名单)<br>
     * 工作簿，列的序号都是从零开始。
     *
     * @param filePath 文件路径
     * @param sheetIndex sheet的索引值，从0开始
     * @param columnIndex 列的索引值，从0开始
     * @return
     */
    static List<String> readColumn(String filePath, int sheetIndex, int columnIndex)
            throws IOException, InvalidFormatException {
        Workbook wb = WorkbookFactory.create(new File(filePath));
        Sheet sheet = wb.getSheetAt(sheetIndex);

        List<String> cols = new ArrayList<String>();
        DataFormatter formatter = new DataFormatter();
        for (Row row : sheet) {

            Cell cell = row.getCell(columnIndex);
            String text = formatter.formatCellValue(cell);

            // 过滤掉表头
            if ("姓名".equals(text)) {
                continue;
            }
//            System.out.println(text);
            cols.add(text);
        }
        wb.close();
        return cols;
    }

    /**
     * 读取整个表的信息。 注意： 由于此错误：
     * org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException: The
     * supplied data appears to be a raw XML file. Formats such as Office 2003
     * XML are not supported，所以POI组件不支持现在的xls文件格式，需要把文件手动打开，
     * 并且另存为标准的excel格式，例如xlsx文件。
     *
     * @param filePath
     * @param sheetIndex
     * @param fromRow 跨过的行数，索引从1计算
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    static List<ExcelRow> readLines(String filePath,
            int sheetIndex,
            int fromRow,
            DateFilter filter)
            throws IOException, InvalidFormatException, Exception {
        Workbook wb = WorkbookFactory.create(new File(filePath));

        Sheet sheet = wb.getSheetAt(sheetIndex);

        List<ExcelRow> rows = new ArrayList<ExcelRow>();
        DataFormatter formatter = new DataFormatter();
        for (Row row : sheet) {
            // 跨国指定列数
            if (fromRow > 1) {
                fromRow--;
                continue;
            }
            ExcelRow line = new ExcelRow();

            int lastColumn = row.getLastCellNum();

            // 如果存在日期过滤条件
            if (filter != null) {
                int dateColumnIndex = filter.getColumnIndex();
                if (dateColumnIndex > lastColumn) {
                    throw new Exception("指定的过滤日期的列数已经超出表格最大列数");
                }
                // 只查找在指定日期之内的数据
                String cellValue = formatter.formatCellValue(
                        row.getCell(dateColumnIndex));
                if (!filter.match(cellValue)) {
                    continue;
                }
            }

            for (int cn = 0; cn < lastColumn; cn++) {
                Cell cell = row.getCell(cn);
                if (cell != null) {
                    String text = formatter.formatCellValue(cell);
//                    System.out.println(text);
                    line.add(cn, text);
                }

            }
            rows.add(line);
        }
        wb.close();
        return rows;

    }

}
